﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Collections;
using System.Data;
using System.IO;
using System.Web;
using FrameworkManapro.Utilities;
using FrameworkManapro.BusinessModel;
using GMAC.ReportingSystem.Business.Entities;
using GMAC.ReportingSystem.Business.Entities.Configuration;

namespace GMAC.ReportingSystem.DataAccess
{
    public class PlantFileDataAccess
    {

        public void GenerateUploadFile(int UserId)
        {
            ArrayList parameters = new ArrayList { UserId };
            DataAccessComponent.ExecuteOperation("procPlantFileMerge", parameters);
        } // GenerateUploadFile


        public StringPlantFileCollection GetPlantFiles(LoadFileStatus status, DateTime processDate, int user)
        {
            StringPlantFileCollection Records = new StringPlantFileCollection();
            StringPlantFile Record;
            ArrayList parameters = new ArrayList { status, processDate, user };
            DataTable dt = DataAccessComponent.ExecuteSelect("procTemporaryPlantFileByFilter", parameters);
            if (dt != null && dt.Rows.Count > 0)
            {
                foreach (DataRow row in dt.Rows)
                {
                    Record = new StringPlantFile()
                    {
                        RetailInvoiceDate               = row["PlantFileRetailInvoiceDate"].ToString(),
                        Dealer                          = row["PlantFileDealer"].ToString(),
                        DealerNumber                    = row["PlantFileDealerNumber"].ToString(),
                        VehicleIdentification           = row["PlantFileVehicleIdentification"].ToString(),
                        ModelCode                       = row["PlantFileModelCode"].ToString(),
                        ModelDescription                = row["PlantFileModelDescription"].ToString(),
                        WholesaleDistributionChannel    = row["PlantFileWholesaleDistributionChannel"].ToString(),
                        Estatus                         = row["PlantFileEstatus"].ToString(),
                        PaymentForm                     = row["PlantFilePaymentForm"].ToString(),
                        PlaquePatent                    = row["PlantFilePlaquePatent"].ToString(),
                        ContactName                     = row["PlantFileContactName"].ToString(),
                        ContactLastName                 = row["PlantFileContactLastName"].ToString(),
                        Year                            = row["PlantFileYear"].ToString(),
                        ContactAddress                  = row["PlantFileContactAddress"].ToString(),
                        ContactMobilePhone              = row["PlantFileContactMobilePhone"].ToString(),
                        residencyTelephoneContact       = row["PlantFileresidencyTelephoneContact"].ToString(),
                        ContactCity                     = row["PlantFileContactCity"].ToString(),
                        DeliveryDate                    = row["PlantFileDeliveryDate"].ToString(),
                        ZoneID                          = row["ZoneID"].ToString(),
                        SalesDetail                     = row["PlantFileSalesDetail"].ToString(),
                        SectorSales                     = row["PlantFileSectorSales"].ToString(),
                        PlanFileType                    = row["PlantFileType"].ToString(),
                        LoadDate                        = (DateTime)row["LoadDate"],
                        RejectionReason                 = row["RejectionReason"].ToString()

                    }; // Record = new StringPlantFile

                    Records.Add(Record);
                } // foreach
            } // if
            return Records;
        } // GetPlantFiles


        /// <summary>
        /// Inserta en una tabla temporal los registros del archivo excel
        /// </summary>
        /// <param name="processDate">Fecha en la que se esta procesando el archivo</param>
        /// <param name="userId">Codigo del usuario que esta procesando el archivo</param>
        /// <param name="records">Coleccion de registros a insertar en la tabla temporal</param>
        /// <param name="planInvalids">Parametro de salida. Coleccion de registros Invalidos encontrados en el archivo</param>
        /// <param name="planValids">Parametro de salida. Coleccion de registros Validos encontrados en el archivo</param>
        public void UploadPlanFile(DateTime processDate, int userId, StringPlantFileCollection records, ref StringPlantFileCollection planInvalids, ref StringPlantFileCollection planValids)
        {
            try
            {
                DataRow row;
                DataTable dt = new DataTable();

                dt.Columns.Add("PlantFileRetailInvoiceDate"             , typeof(string));
                dt.Columns.Add("PlantFileDealer"                        , typeof(string));
                dt.Columns.Add("PlantFileDealerNumber"                  , typeof(string));
                dt.Columns.Add("PlantFileVehicleIdentification"         , typeof(string));
                dt.Columns.Add("PlantFileModelCode"                     , typeof(string));
                dt.Columns.Add("PlantFileModelDescription"              , typeof(string));
                dt.Columns.Add("PlantFileWholesaleDistributionChannel"  , typeof(string));
                dt.Columns.Add("PlantFileEstatus"                       , typeof(string));
                dt.Columns.Add("PlantFilePaymentForm"                   , typeof(string));
                dt.Columns.Add("PlantFilePlaquePatent"                  , typeof(string));
                dt.Columns.Add("PlantFileContactName"                   , typeof(string));
                dt.Columns.Add("PlantFileContactLastName"               , typeof(string));
                dt.Columns.Add("PlantFileYear"                          , typeof(string));
                dt.Columns.Add("PlantFileContactAddress"                , typeof(string));
                dt.Columns.Add("PlantFileContactMobilePhone"            , typeof(string));
                dt.Columns.Add("PlantFileresidencyTelephoneContact"     , typeof(string));
                dt.Columns.Add("PlantFileContactCity"                   , typeof(string));
                dt.Columns.Add("PlantFileDeliveryDate"                  , typeof(string));
                dt.Columns.Add("ZoneID"                                 , typeof(string));
                dt.Columns.Add("PlantFileSalesDetail"                   , typeof(string));
                dt.Columns.Add("PlantFileSectorSales"                   , typeof(string));
                dt.Columns.Add("PlantFileType"                          , typeof(string));
                dt.Columns.Add("LoadDate"                               , typeof(DateTime));
                dt.Columns.Add("UserID"                                 , typeof(string));


                foreach (StringPlantFile Record in records)
                {
                    row = dt.NewRow();


                    row["PlantFileRetailInvoiceDate"]               = Record.RetailInvoiceDate;
                    row["PlantFileDealer"]                          = Record.Dealer;
                    row["PlantFileDealerNumber"]                    = Record.DealerNumber;
                    row["PlantFileVehicleIdentification"]           = Record.VehicleIdentification;
                    row["PlantFileModelCode"]                       = Record.ModelCode;
                    row["PlantFileModelDescription"]                = Record.ModelDescription;
                    row["PlantFileWholesaleDistributionChannel"]    = Record.WholesaleDistributionChannel;
                    row["PlantFileEstatus"]                         = Record.Estatus;
                    row["PlantFilePaymentForm"]                     = Record.PaymentForm;
                    row["PlantFilePlaquePatent"]                    = Record.PlaquePatent;
                    row["PlantFileContactName"]                     = Record.ContactName;
                    row["PlantFileContactLastName"]                 = Record.ContactLastName;
                    row["PlantFileYear"]                            = Record.Year;
                    row["PlantFileContactAddress"]                  = Record.ContactAddress;
                    row["PlantFileContactMobilePhone"]              = Record.ContactMobilePhone;
                    row["PlantFileresidencyTelephoneContact"]       = Record.residencyTelephoneContact;
                    row["PlantFileContactCity"]                     = Record.ContactCity;
                    row["PlantFileDeliveryDate"]                    = Record.DeliveryDate;
                    row["ZoneID"]                                   = Record.ZoneID;
                    row["PlantFileSalesDetail"]                     = Record.SalesDetail;
                    row["PlantFileSectorSales"]                     = Record.SectorSales;
                    row["PlantFileType"]                            = Record.PlanFileType;
                    row["LoadDate"]                                 = (DateTime)Record.LoadDate;
                    //row["RejectionReason"]                          = Record.RejectionReason;
                    row["UserID"]                                   = userId;

                    // Inserta el registro como Correcto 
                    // y despues se ejecuta la validacion en el SP que puede cambiar el estatus a invalido
                    //row["bitStatus"]            = OK;

                    dt.Rows.Add(row);
                } // foreach

                // asigna los parametros para llamar el SP
                var parameters = new ArrayList { processDate, userId, dt };

                // Llama el SP que inserta en la tabla temporal
                DataAccessComponent.ExecuteOperation("procTemporaryPlantFileInsert", parameters);

                // Llama los metodos que obtiene la lista de registros validos e invalidos para retornarlos en la funcion
                planInvalids = GetPlantFiles(LoadFileStatus.Invalid, processDate, userId);
                planValids = GetPlantFiles(LoadFileStatus.Valid, processDate, userId);
            } // try
            catch (Exception)
            {
                throw;
            } // catch


        } // UploadPlanFile


        /// <summary>
        /// Recibe una coleccion y la retorna ordenada segun indiquen los parametros
        /// </summary>
        /// <param name="records">Coleccion de valores a ordenar</param>
        /// <param name="column">Campo por el que va a ordenar</param>
        /// <param name="order">"ASC" o "DESC"</param>
        /// <returns>Lista ordenada</returns>
        public StringPlantFileCollection sord(StringPlantFileCollection records, string column, string order)
        {
            StringPlantFileCollection Result = new StringPlantFileCollection();
            StringPlantFile NewRecord;
            DataRow row;
            DataTable dt = new DataTable();

            dt.Columns.Add("PlantFileRetailInvoiceDate", typeof(string));
            dt.Columns.Add("PlantFileDealer", typeof(string));
            dt.Columns.Add("PlantFileDealerNumber", typeof(string));
            dt.Columns.Add("PlantFileVehicleIdentification", typeof(string));
            dt.Columns.Add("PlantFileModelCode", typeof(string));
            dt.Columns.Add("PlantFileModelDescription", typeof(string));
            dt.Columns.Add("PlantFileWholesaleDistributionChannel", typeof(string));
            dt.Columns.Add("PlantFileEstatus", typeof(string));
            dt.Columns.Add("PlantFilePaymentForm", typeof(string));
            dt.Columns.Add("PlantFilePlaquePatent", typeof(string));
            dt.Columns.Add("PlantFileContactName", typeof(string));
            dt.Columns.Add("PlantFileContactLastName", typeof(string));
            dt.Columns.Add("PlantFileYear", typeof(string));
            dt.Columns.Add("PlantFileContactAddress", typeof(string));
            dt.Columns.Add("PlantFileContactMobilePhone", typeof(string));
            dt.Columns.Add("PlantFileresidencyTelephoneContact", typeof(string));
            dt.Columns.Add("PlantFileContactCity", typeof(string));
            dt.Columns.Add("PlantFileDeliveryDate", typeof(string));
            dt.Columns.Add("ZoneID", typeof(string));
            dt.Columns.Add("PlantFileSalesDetail", typeof(string));
            dt.Columns.Add("PlantFileSectorSales", typeof(string));
            dt.Columns.Add("PlantFileType", typeof(string));
            dt.Columns.Add("LoadDate", typeof(DateTime));
            dt.Columns.Add("UserID", typeof(string));
            dt.Columns.Add("RejectionReason", typeof(string));
            dt.Columns.Add("RejectionReason_Small", typeof(string));

            // Captura todos los registros de la coleccion en la tabla para ordenarlos
            foreach (StringPlantFile Record in records)
            {
                row = dt.NewRow();

                row["PlantFileRetailInvoiceDate"] = Record.RetailInvoiceDate;
                row["PlantFileDealer"] = Record.Dealer;
                row["PlantFileDealerNumber"] = Record.DealerNumber;
                row["PlantFileVehicleIdentification"] = Record.VehicleIdentification;
                row["PlantFileModelCode"] = Record.ModelCode;
                row["PlantFileModelDescription"] = Record.ModelDescription;
                row["PlantFileWholesaleDistributionChannel"] = Record.WholesaleDistributionChannel;
                row["PlantFileEstatus"] = Record.Estatus;
                row["PlantFilePaymentForm"] = Record.PaymentForm;
                row["PlantFilePlaquePatent"] = Record.PlaquePatent;
                row["PlantFileContactName"] = Record.ContactName;
                row["PlantFileContactLastName"] = Record.ContactLastName;
                row["PlantFileYear"] = Record.Year;
                row["PlantFileContactAddress"] = Record.ContactAddress;
                row["PlantFileContactMobilePhone"] = Record.ContactMobilePhone;
                row["PlantFileresidencyTelephoneContact"] = Record.residencyTelephoneContact;
                row["PlantFileContactCity"] = Record.ContactCity;
                row["PlantFileDeliveryDate"] = Record.DeliveryDate;
                row["ZoneID"] = Record.ZoneID;
                row["PlantFileSalesDetail"] = Record.SalesDetail;
                row["PlantFileSectorSales"] = Record.SectorSales;
                row["PlantFileType"] = Record.PlanFileType;
                row["LoadDate"] = (DateTime)Record.LoadDate;
                row["UserID"] = Record.UserID.ToString();
                row["RejectionReason"] = Record.RejectionReason;
                row["RejectionReason_Small"] = Record.RejectionReason_Small;

                dt.Rows.Add(row);
            } // foreach


            // Ordena los valores
            dt.DefaultView.Sort = "PlantFile" + column + " " + order;

            dt = dt.DefaultView.ToTable();

            // vuelve a asignar los registros en la coleccion con el nuevo orden
            if (dt != null && dt.Rows.Count > 0)
            {
                foreach (DataRow newrow in dt.Rows)
                {
                    NewRecord = new StringPlantFile
                    {
                        RetailInvoiceDate               = newrow["PlantFileRetailInvoiceDate"].ToString(),
                        Dealer                          = newrow["PlantFileDealer"].ToString(),
                        DealerNumber                    = newrow["PlantFileDealerNumber"].ToString(),
                        VehicleIdentification           = newrow["PlantFileVehicleIdentification"].ToString(),
                        ModelCode                       = newrow["PlantFileModelCode"].ToString(),
                        ModelDescription                = newrow["PlantFileModelDescription"].ToString(),
                        WholesaleDistributionChannel    = newrow["PlantFileWholesaleDistributionChannel"].ToString(),
                        Estatus                         = newrow["PlantFileEstatus"].ToString(),
                        PaymentForm                     = newrow["PlantFilePaymentForm"].ToString(),
                        PlaquePatent                    = newrow["PlantFilePlaquePatent"].ToString(),
                        ContactName                     = newrow["PlantFileContactName"].ToString(),
                        ContactLastName                 = newrow["PlantFileContactLastName"].ToString(),
                        Year                            = newrow["PlantFileYear"].ToString(),
                        ContactAddress                  = newrow["PlantFileContactAddress"].ToString(),
                        ContactMobilePhone              = newrow["PlantFileContactMobilePhone"].ToString(),
                        residencyTelephoneContact       = newrow["PlantFileresidencyTelephoneContact"].ToString(),
                        ContactCity                     = newrow["PlantFileContactCity"].ToString(),
                        DeliveryDate                    = newrow["PlantFileDeliveryDate"].ToString(),
                        ZoneID                          = newrow["ZoneID"].ToString(),
                        SalesDetail                     = newrow["PlantFileSalesDetail"].ToString(),
                        SectorSales                     = newrow["PlantFileSectorSales"].ToString(),
                        PlanFileType                    = newrow["PlantFileType"].ToString(),
                        LoadDate                        = (DateTime)newrow["LoadDate"],
                        RejectionReason                 = newrow["RejectionReason"].ToString()

                    }; // Record = new

                    Result.Add(NewRecord);
                } // foreach
            } // if

            // Retorna los valores en la coleccion ordenados
            return Result;

        } // sord


    } // class
} // namespace
